package com.ncloud.resman.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.alibaba.fastjson.JSONObject;
import com.ncloud.resman.entity.Cabinet;
import com.ncloud.resman.entity.CabinetExcel;

@Mapper
public interface CabinetMapper{

	@Select("SELECT "
			+ "CONCAT(t1.cabinet_id,'') as cabinet_id, t1.cabinet_code, CONCAT(t1.room_id,'') as room_id, "
			+ "t2.room_name, CONCAT(t2.area_id,'') as area_id, t2.area_name, CONCAT(t2.country_id,'') as country_id, t2.country_name, "
			+ "CONCAT(t2.province_id,'') as province_id, t2.province_name, CONCAT(t2.city_id,'') as city_id, t2.city_name,"
			+ "CONCAT(t1.place_num,'') as place_num, CONCAT(t1.state,'') as state, "
			+ "(select count(1) from res_cabinet_use t3 where t3.cabinet_id=t1.cabinet_id and t3.state=1) as use_num,"
			+ "CONCAT(t1.create_user,'') as create_user,"
			+ "date_format(t1.create_date, '%Y-%m-%d %H:%i:%s' ) as create_date, CONCAT(t1.update_user,'') as update_user, "
			+ "date_format(t1.update_date, '%Y-%m-%d %H:%i:%s' ) as update_date, t1.remarks "
			+ "FROM res_cabinet t1,sys_computer_room t2 "
			+ "where t1.room_id=t2.room_id "
			+ "and (#{0}=0 or t2.area_id = #{0}) "
			+ "and (#{1}=0 or t2.country_id = #{1}) "
			+ "and (#{2}=0 or t2.province_id = #{2}) "
			+ "and (#{3}=0 or t2.city_id = #{3}) "
			+ "and (#{4}=0 or t1.room_id = #{4}) "
			+ "and (#{5}=0 or t1.place_num = #{5}) "
			+ "and (#{6}=2 or t1.state = #{6}) ")
	@Results({
		@Result(column="cabinet_id",property="cabinetId"),@Result(column="cabinet_code",property="cabinetCode"),
		@Result(column="room_id",property="roomId"),@Result(column="room_name",property="roomName"),
		@Result(column="area_id",property="areaId"),@Result(column="area_name",property="areaName"),
		@Result(column="country_id",property="countryId"),@Result(column="country_name",property="countryName"),
		@Result(column="province_id",property="provinceId"),@Result(column="province_name",property="provinceName"),
		@Result(column="city_id",property="cityId"),@Result(column="city_name",property="cityName"),
		@Result(column="place_num",property="placeNum"),@Result(column="state",property="state"),
		@Result(column="use_num",property="useNum"),
		@Result(column="create_user",property="createUser"),@Result(column="create_date",property="createDate"),
		@Result(column="update_user",property="updateUser"),@Result(column="update_date",property="updateDate"),
		@Result(column="remarks",property="remarks")})
	List<JSONObject> getCabinetInfo(Integer areaId,Integer countryId,Integer provinceId,Integer cityId,Integer roomId,Integer placeNum,Integer state);
	
	@Select("SELECT CONCAT(t1.cabinet_id,'') as cabinet_id, t1.cabinet_code, CONCAT(t1.room_id,'') as room_id, "
			+ "t2.room_name, CONCAT(t2.area_id,'') as area_id, t2.area_name, CONCAT(t2.country_id,'') as country_id, t2.country_name, "
			+ "CONCAT(t2.province_id,'') as province_id, t2.province_name, CONCAT(t2.city_id,'') as city_id, t2.city_name,t2.addr,"
			+ "CONCAT(t1.place_num,'') as place_num, "
			+ "(case when state=0 then '空闲' when state=1 then '使用中' else '空闲' end) as state , "
			+ "(select count(1) from res_cabinet_use t3 where t3.cabinet_id=t1.cabinet_id and t3.state=1) as use_num "
			+ "FROM res_cabinet t1,sys_computer_room t2 "
			+ "where t1.room_id=t2.room_id ")
	@Results({
		@Result(column="cabinet_id",property="cabinetId"),@Result(column="cabinet_code",property="cabinetCode"),
		@Result(column="room_id",property="roomId"),@Result(column="room_name",property="roomName"),
		@Result(column="area_id",property="areaId"),@Result(column="area_name",property="areaName"),
		@Result(column="country_id",property="countryId"),@Result(column="country_name",property="countryName"),
		@Result(column="province_id",property="provinceId"),@Result(column="province_name",property="provinceName"),
		@Result(column="city_id",property="cityId"),@Result(column="city_name",property="cityName"),
		@Result(column="addr",property="addr"),@Result(column="place_num",property="placeNum"),
		@Result(column="state",property="state"),@Result(column="use_num",property="useNum")})
	List<CabinetExcel> getCabinetInfoExport();
	
	@Select("SELECT "
			+ "CONCAT(t1.cabinet_id,'') as cabinet_id, t1.cabinet_code, CONCAT(t1.room_id,'') as room_id, "
			+ "t1.place_num, t1.state "
			+ "FROM res_cabinet t1 "
			+ "where t1.room_id = #{0} "
			+ "and t1.cabinet_id = #{1}")
	@Results({
		@Result(column="cabinet_id",property="cabinetId"),@Result(column="cabinet_code",property="cabinetCode"),
		@Result(column="room_id",property="roomId"),
		@Result(column="place_num",property="placeNum"),@Result(column="state",property="state")})
	List<JSONObject> getCabinetInfoById(Integer roomId,Long cabinetId);//根据机房编码和机柜编码查询机柜信息
	
	@Select("SELECT CONCAT(room_id,'') as room_id, room_name FROM sys_computer_room where room_name = #{roomName} ")
	@Results({
		@Result(column="room_id",property="roomId"),@Result(column="room_name",property="roomName")})
	List<JSONObject> getRoomIdByName(String roomName);//根据机房名称获取机房id
	
	@Select("SELECT CONCAT(room_id,'') as room_id, room_name FROM sys_computer_room where room_id = #{roomId} ")
	@Results({
		@Result(column="room_id",property="roomId"),@Result(column="room_name",property="roomName")})
	List<JSONObject> getRoomById(Integer roomId);//根据机房id获取机房id
	
	@Select("SELECT cabinet_id,cabinet_code FROM res_cabinet where room_id = #{0} and cabinet_code=#{1} ")
	@Results({@Result(column="cabinet_id",property="cabinetId"),@Result(column="cabinet_code",property="cabinetCode")})
	List<JSONObject> getCabinetByCode(Integer roomId,String cabinetCode);//根据机房id和机柜编码
	
	@Insert(value = "INSERT INTO res_cabinet (cabinet_id, cabinet_code, room_id, place_num, state, create_user, create_date, update_user, update_date, remarks) "
			+ "values(#{cabinetId},#{cabinetCode},#{roomId},#{placeNum},#{state},#{createUser},#{createDate},#{updateUser},#{updateDate},#{remarks})")
    public int insertCabinet(Cabinet cabinet);
	
	@Update(value = "UPDATE res_cabinet SET cabinet_code = #{cabinetCode}, room_id = #{roomId}, place_num = #{placeNum}, state = #{state}, "
			+ "update_user = #{updateUser}, update_date = #{updateDate}, remarks = #{remarks} WHERE cabinet_id = #{cabinetId}")
    public int updateCabinet(Cabinet cabinet);
	
	@Delete(value = "delete from res_cabinet WHERE cabinet_id = #{cabinetId}")
    public int deleteCabinet(Long cabinetId);
	
	@Select(value = "select cabinet_id,room_id,state from res_cabinet where cabinet_code=#{0}")
	@Results({
		@Result(column="cabinet_id",property="cabinetId"),
		@Result(column="room_id",property="roomId"),@Result(column="state",property="state")})
	Cabinet getRommIdByCabinetCode(String cabinetCaode); 
}
